import datetime
import sys
import time

import pymysql as pymysql
from pymysql.constants import CLIENT

from src.utils.LogUtils import Logger


# DB基类
class MysqlClientBase:

    def __init__(self, host, port, user, pwd, db, logSql=False, autocommit=True, bDict=True):
        self._logger_ = Logger()
        self._alive = datetime.datetime.now()
        self._host = host
        self._port = port
        self._user = user
        self._pwd = pwd
        self._db = db
        self._logSql = logSql
        self._connection = None
        self._cursor = None
        self._bDict = bDict
        # noinspection PyBroadException
        try:
            self._connection = pymysql.connect(
                host=host,
                port=port,
                user=user,
                password=pwd,
                database=db,
                autocommit=autocommit,
                client_flag=CLIENT.MULTI_STATEMENTS,
                cursorclass=(pymysql.cursors.DictCursor if self._bDict else pymysql.cursors.Cursor))
            self._cursor = self._connection.cursor()
            self._logger_.info('数据库已连接 host=' + host + ', db=' + db)
        except Exception as e:
            self._logger_.error(e)
            self._logger_.error("数据库连接失败")
            sys.exit('数据库连接失败')

    # def __del__(self):
    #     if self._cursor:
    #         self._cursor.close()
    #     if self._connection:
    #         self._connection.close()

    def getAutoCommit(self):
        return self._connection.get_autocommit()

    def setAutoCommit(self, value):
        self._connection.autocommit(value)

    def commit(self):
        self._connection.commit()

    def rollback(self):
        self._connection.rollback()

    # 执行SQL
    def Query(self, sql, sql_param_list=None) -> any:
        """
        执行查询类SQL
        :param sql: SQL
        :param sql_param_list: 参数list
        :return: 查询结果
        """
        if self._logSql:
            self._logger_.debug('[%-15s %-15s execute: %s, sql_param_list: %s' % (self._host, self._db, sql, sql_param_list))
            t = time.perf_counter()
            if sql_param_list is None:
                self._cursor.execute(sql)
            else:
                self._cursor.execute(sql, sql_param_list)
            ret = self._cursor.fetchall()
            cast = (time.perf_counter() - t) * 1000
            self._logger_.debug('execute 耗时: %.2f 毫秒' % cast)
        else:
            self._cursor.execute(sql, sql_param_list)
            ret = self._cursor.fetchall()

        if type(ret) is tuple:
            ret = list(ret)
        return ret

    # 执行SQL
    def QueryOne(self, sql, sql_param_list=None) -> any:
        """
        执行查询类SQL
        :param sql: SQL
        :param sql_param_list: 参数list
        :return: 查询结果
        """
        if self._logSql:
            self._logger_.debug('[%-15s %-15s execute: %s, sql_param_list: %s' % (self._host, self._db, sql, sql_param_list))
            t = time.perf_counter()
            if sql_param_list is None:
                self._cursor.execute(sql)
            else:
                self._cursor.execute(sql, sql_param_list)
            ret = self._cursor.fetchall()
            cast = (time.perf_counter() - t) * 1000
            self._logger_.debug('execute 耗时: %.2f 毫秒' % cast)
        else:
            self._cursor.execute(sql, sql_param_list)
            ret = self._cursor.fetchall()

        if len(ret) > 0:
            return ret[0]
        else:
            return None

    # 执行SQL
    def ExecMany(self, sql, datas: list, size: int = 1000) -> int:
        """
        执行批量SQL
        :param sql: SQL模板
        :param datas: 数据list
        :param size:
        :return:
        """
        if self._logSql:
            self._logger_.debug('[%-15s %-15s execute: %s, list: %s' % (self._host, self._db, sql, len(datas)))
            t = time.perf_counter()
            ret = 0
            for start in range(0, len(datas), size):
                subDatas = datas[start: start + size]
                ret += self._cursor.executemany(sql, subDatas)
            cast = (time.perf_counter() - t) * 1000
            self._logger_.debug('execute 耗时: %.2f 毫秒' % cast)
            return ret
        else:
            ret = self._cursor.executemany(sql, datas)

        return ret

    # 执行Sql
    def ExecNoneQuery(self, sql, sql_param_list=None) -> int:
        """
        执行非查询SQL
        :param sql: SQL
        :param sql_param_list: 参数
        :return:
        """
        if self._logSql:
            self._logger_.debug('[%-15s %-15s execute: %s, sql_param_list: %s' % (self._host, self._db, sql, sql_param_list))
            t = time.perf_counter()
            if sql_param_list is None:
                ret = self._cursor.execute(sql)
            else:
                ret = self._cursor.execute(sql, sql_param_list)
            cast = (time.perf_counter() - t) * 1000
            self._logger_.debug('execute 耗时: %.2f 毫秒' % cast)
        else:
            ret = self._cursor.execute(sql, sql_param_list)

        return ret
